package me.baicai.modules.belldandy.gcb.repository;

import com.alibaba.fastjson.JSONObject;
import me.baicai.modules.belldandy.gcb.domain.UserProject;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;


/**
* @author 超级大白菜
* @date 2020-04-01
*/
public interface UserProjectRepository extends JpaRepository<UserProject, Integer>, JpaSpecificationExecutor<UserProject> {

    void deleteAllByPid(Integer id);

    @Query(value = "select p.*,u.type,u.commission from user_project u LEFT JOIN project p on p.id = u.pid where u.uid = ?1", nativeQuery = true)
    List<JSONObject> getUserProject(Integer uid);

    @Query(value = "select u.type,u.commission,c.* from user_project u RIGHT JOIN  crm_order c on c.projectid = u.pid and DATE_FORMAT( c.created, '%Y-%m' ) = ?3 where u.uid = ?1 and u.pid = ?2", nativeQuery = true)
    List<JSONObject> projectInfo(Integer uid, Integer pid, String date);
}
